package jp.co.technotree.common.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import jp.co.technotree.common.util.DBGLog;
import jp.co.technotree.exception.DataAccessException;
import jp.co.technotree.exception.DataAccessObjectException;
import jp.co.technotree.exception.StatementParameterException;
import jp.co.technotree.exception.TransactionException;


public class BaseDAO {
	private static String CLASSNAME=BaseDAO.class.getName();
	private BaseDAO parentDAO = null;
	
	public BaseDAO(){
	}

	public BaseDAO(BaseDAO parent){
		this.setParentDAO(parent);
	}

	public void setParentDAO(BaseDAO parent){
		this.parentDAO = parent;
	}

	protected Connection getConnection() throws TransactionException{
		String METHODNAME = "getConnection";
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"START");
		Connection con = null;
		
		if ( this.parentDAO == null ){
			// 親の指定が無い場合：セッションレベルトランザクションを使用
			Transaction trx = TransactionManager.getTransaction();
			con = trx.getConnection();
		}else{
			// 親の指定がある場合場合：セッションレベルトランザクションを使用
			con = this.parentDAO.getConnection();
		}
		
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"END");
		return con;
	}
	
	/**
	 * SQL実行
	 * @param sql
	 * @return
	 * @throws TransactionException 
	 */
	protected ReadOnlyRowSet executeSQL(String sql) throws DataAccessObjectException, TransactionException{
		return executeSQL(sql,0,0);
	}
	
	/**
	 * SQL実行
	 * @param sql
	 * @param startRows
	 * @return
	 * @throws TransactionException 
	 */
	protected ReadOnlyRowSet executeSQL(String sql,int startRows) throws DataAccessObjectException, TransactionException{
		return executeSQL(sql,startRows,0);
	}

	/**
	 * SQL実行
	 * @param sql
	 * @param startRows
	 * @param getRecordCount
	 * @return
	 * @throws TransactionException 
	 */
	protected ReadOnlyRowSet executeSQL(String sql,int startRows,int getRecordCount) throws DataAccessObjectException, TransactionException{
		String METHODNAME = "executeSQL";
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"START");
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet resultSet = null;
		ReadOnlyRowSet rowSet = new ReadOnlyRowSet();
		long startTime = 0, serverStopTime = 0, instanceStopTime = 0;

		conn = getConnection();
		try {
			startTime = System.currentTimeMillis();
			stmt = conn.createStatement();
			resultSet = stmt.executeQuery(sql);
			serverStopTime = System.currentTimeMillis();
			rowSet.populate(resultSet,startRows,getRecordCount);
			instanceStopTime = System.currentTimeMillis();
			DBGLog.fwdebug(CLASSNAME,METHODNAME,"SQL実行時間:" + (instanceStopTime - startTime) + "ms 実行完了速度:"+ (serverStopTime - startTime) + "ms");
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessObjectException("データ取得中にエラーが発生しました。:"+e.getMessage());
		} finally {
			try {
				if ( resultSet != null ) resultSet.close();
				if ( stmt != null ) stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"END");
		return rowSet;
	}
	
	
	/**
	 * 
	 * @param sql
	 * @param startRows
	 * @param getRecordCount
	 * @return
	 * @throws DataAccessObjectException
	 * @throws TransactionException 
	 */
	protected int executeUpdateSQL(String sql) throws DataAccessObjectException, TransactionException{
		String METHODNAME = "executeSQL";
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"START");
		
		Connection conn = null;
		Statement stmt = null;
		int result = 0;
		long startTime = 0, stopTime = 0;

		conn = getConnection();
		try {
			startTime = System.currentTimeMillis();
			
			stmt = conn.createStatement();
			result = stmt.executeUpdate(sql);
			
			stopTime = System.currentTimeMillis();
			DBGLog.fwdebug(CLASSNAME,METHODNAME,"更新SQL実行時間:" + (stopTime - startTime) + "ms");
			DBGLog.fwdebug(CLASSNAME,METHODNAME,"データ更新件数 "+result+"件");
		} catch (SQLException e) {
			DataAccessObjectException exception = new DataAccessObjectException("データ更新中にエラーが発生しました。:"+e.getMessage()+"\n"+sql);
			exception.addException(e);
			throw exception; 
		} finally {
			try {
				if ( stmt != null ) stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"END");
		return result;
	}
	
	/**
	 * SQL実行
	 * @param sql
	 * @param startRows
	 * @param getRecordCount
	 * @return
	 * @throws TransactionException 
	 */
	protected ReadOnlyRowSet executeSQL(String sql,StatementParameters parameters,int startRows,int getRecordCount) throws DataAccessObjectException, TransactionException{
		String METHODNAME = "executeSQL";
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"START");
		
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet resultSet = null;
		ReadOnlyRowSet rowSet = new ReadOnlyRowSet();
		long startTime = 0, stopTime = 0;

		conn = getConnection();
		try {
			startTime = System.currentTimeMillis();
			
			
			// StatementParameters から preStmt にパラメーターをセット
			StatementParser parser = new StatementParser(sql);
			String parseSql = parser.getSql();
			preStmt = conn.prepareStatement(parseSql);
			
			parser.setParameters(preStmt,parameters);
			
			//
			resultSet = preStmt.executeQuery();
			rowSet.populate(resultSet,startRows,getRecordCount);
			stopTime = System.currentTimeMillis();
			DBGLog.fwdebug(CLASSNAME,METHODNAME,"SQL実行時間:" + (stopTime - startTime) + "ms");
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessObjectException("データ取得中にエラーが発生しました。:"+e.getMessage()+"\n"+sql+"\nパラメーター:\n"+parameters);
		} catch(StatementParameterException e){
			e.printStackTrace();
			throw new DataAccessObjectException("データ取得中にエラーが発生しました。:"+e.getMessage()+"\n"+sql+"\nパラメーター:\n"+parameters);
		} catch(Exception e){
			e.printStackTrace();
			throw new DataAccessObjectException("予期せぬエラーが発生しました。:"+e.getMessage()+"\n"+sql+"\nパラメーター:\n"+parameters);
		} finally {
			try {
				if ( resultSet != null ) resultSet.close();
				if ( preStmt != null ) preStmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"END");
		return rowSet;
	}
	
	/**
	 * 
	 * @param sql
	 * @param startRows
	 * @param getRecordCount
	 * @return
	 * @throws DataAccessObjectException
	 * @throws TransactionException 
	 */
	protected int executeUpdateSQL(String sql,StatementParameters parameters) throws DataAccessObjectException, TransactionException{
		String METHODNAME = "executeSQL";
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"START");
		
		Connection conn = null;
		PreparedStatement preStmt = null;
		int result = 0;
		long startTime = 0, stopTime = 0;

		conn = getConnection();
		try {
			startTime = System.currentTimeMillis();
			// StatementParameters から preStmt にパラメーターをセット
			StatementParser parser = new StatementParser(sql);
			String parseSql = parser.getSql();
			DBGLog.fwdebug(CLASSNAME,METHODNAME,"更新SQL:" + parseSql);
			
			preStmt = conn.prepareStatement(parseSql);
			parser.setParameters(preStmt,parameters);
			
			result = preStmt.executeUpdate();
			
			stopTime = System.currentTimeMillis();
			DBGLog.fwdebug(CLASSNAME,METHODNAME,"更新SQL実行時間:" + (stopTime - startTime) + "ms");
			DBGLog.fwdebug(CLASSNAME,METHODNAME,"データ更新件数 "+result+"件");
		} catch (SQLException e) {
			e.printStackTrace();
			DataAccessObjectException exception = new DataAccessObjectException("データ更新中にエラーが発生しました。:"+e.getMessage()+"\n"+sql+"\nパラメーター:\n"+parameters);
			exception.addException(e);
			throw exception; 
		} catch(StatementParameterException e){
			e.printStackTrace();
			throw new DataAccessObjectException("データ更新中にエラーが発生しました。:"+e.getMessage()+"\n"+sql+"\nパラメーター:\n"+parameters);
		} catch(Exception e){
			e.printStackTrace();
			throw new DataAccessObjectException("予期せぬエラーが発生しました。:"+e.getMessage()+"\n"+sql+"\nパラメーター:\n"+parameters);
		} finally {
			try {
				if ( preStmt != null ) preStmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"END");
		return result;
	}
	
	/**
	 * バッチ実行用ステートメントの作成
	 * @param sql
	 * @return
	 * @throws DataAccessObjectException
	 * @throws TransactionException
	 */
	protected StatementBatchParameters createUpdateStatement(String sql) throws DataAccessObjectException, TransactionException{
		String METHODNAME = "createUpdateStatement";
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"START");
		
		Connection conn = null;
		PreparedStatement preStmt = null;
		StatementBatchParameters stmtBatchParams = null;
		
		conn = getConnection();
		try {
			// StatementParameters から preStmt にパラメーターをセット
			StatementParser parser = new StatementParser(sql);
			String parseSql = parser.getSql();
			DBGLog.fwdebug(CLASSNAME,METHODNAME,"更新SQL:" + parseSql);
			
			preStmt = conn.prepareStatement(parseSql);
			
			stmtBatchParams = new StatementBatchParameters(conn,parser,preStmt);
			
		} catch (SQLException e) {
			e.printStackTrace();
			DataAccessObjectException exception = new DataAccessObjectException("データ更新中にエラーが発生しました。:"+e.getMessage()+"\n"+sql);
			exception.addException(e);
			throw exception; 
		} catch(Exception e){
			e.printStackTrace();
			throw new DataAccessObjectException("予期せぬエラーが発生しました。:"+e.getMessage()+"\n"+sql);
		} finally {
		}
		
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"END");
		return stmtBatchParams;
	}
	
	/**
	 * バッチに蓄積する
	 * @param stmtBatchParams
	 * @param parameters
	 * @return
	 * @throws DataAccessObjectException
	 * @throws TransactionException
	 */
	protected int addUpdateSQL(StatementBatchParameters stmtBatchParams,StatementParameters parameters) throws DataAccessObjectException, TransactionException{
		String METHODNAME = "addUpdateSQL";
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"START");
		
		PreparedStatement preStmt = null;
		int result = 0;

		try {
			// StatementParameters から preStmt にパラメーターをセット
			StatementParser parser = stmtBatchParams.getStatementParser();
			preStmt = stmtBatchParams.getPreparedStatement();
			parser.setParameters(preStmt,parameters);
			preStmt.addBatch();
		} catch (SQLException e) {
			e.printStackTrace();
			DataAccessObjectException exception = new DataAccessObjectException("データ更新中にエラーが発生しました。:"+e.getMessage());
			exception.addException(e);
			throw exception; 
		} catch(StatementParameterException e){
			e.printStackTrace();
			throw new DataAccessObjectException("データ更新中にエラーが発生しました。:"+e.getMessage());
		} catch(Exception e){
			e.printStackTrace();
			throw new DataAccessObjectException("予期せぬエラーが発生しました。:"+e.getMessage());
		} finally {
		}
		
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"END");
		return result;
	}

	/**
	 * バッチに蓄積したSQLを一括実行
	 * @param stmtBatchParams
	 * @return
	 * @throws DataAccessObjectException
	 * @throws TransactionException
	 */
	protected int executeUpdateSQL(StatementBatchParameters stmtBatchParams) throws DataAccessObjectException, TransactionException{
		String METHODNAME = "executeUpdateSQL";
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"START");
		
		PreparedStatement preStmt = null;
		int result[] = null;
		int resultCnt = 0;
		long startTime = 0, stopTime = 0;

		try {
			startTime = System.currentTimeMillis();
			
			preStmt = stmtBatchParams.getPreparedStatement();
			result = preStmt.executeBatch();
			if ( result != null ){
				for ( int i=0; i<result.length; i++ ){
					resultCnt+=result[i];
				}
			}
			stopTime = System.currentTimeMillis();
			DBGLog.fwdebug(CLASSNAME,METHODNAME,"更新SQL実行時間:" + (stopTime - startTime) + "ms");
			DBGLog.fwdebug(CLASSNAME,METHODNAME,"データ更新件数 "+resultCnt+"件");
		} catch (SQLException e) {
			e.printStackTrace();
			DataAccessObjectException exception = new DataAccessObjectException("データ更新中にエラーが発生しました。:"+e.getMessage());
			exception.addException(e);
			throw exception; 
		} catch(Exception e){
			e.printStackTrace();
			throw new DataAccessObjectException("予期せぬエラーが発生しました。:"+e.getMessage());
		} finally {
			try {
				if ( preStmt != null ) preStmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"END");
		return resultCnt;
	}

	/**
	 * 
	 * @param sql
	 * @param startRows
	 * @param getRecordCount
	 * @return
	 * @throws DataAccessObjectException
	 * @throws TransactionException 
	 */
	protected int executeInsertSQL(String tableName,StatementParameters parameters) throws DataAccessObjectException, TransactionException{
		String METHODNAME = "executeSQL";
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"START");
		
		Connection conn = null;
		PreparedStatement preStmt = null;
		int result = 0;
		long startTime = 0, stopTime = 0;
		String parseSql = null;

		conn = getConnection();
		try {
			startTime = System.currentTimeMillis();
			// StatementParameters から preStmt にパラメーターをセット
			StatementCreator creator = new StatementCreator();
			parseSql = creator.insert(tableName, parameters);

			StatementParser parser = new StatementParser(parseSql);
			parseSql = parser.getSql();

			preStmt = conn.prepareStatement(parseSql);
			parser.setParameters(preStmt,parameters);

			result = preStmt.executeUpdate();
			
			stopTime = System.currentTimeMillis();
			DBGLog.fwdebug(CLASSNAME,METHODNAME,"更新SQL実行時間:" + (stopTime - startTime) + "ms");
			DBGLog.fwdebug(CLASSNAME,METHODNAME,"データ更新件数 "+result+"件");
		} catch (SQLException e) {
			e.printStackTrace();
			DataAccessObjectException exception = new DataAccessObjectException("データ更新中にエラーが発生しました。:"+e.getMessage()+"\n"+parseSql+"\nパラメーター:\n"+parameters);
			exception.addException(e);
			throw exception; 
		} catch(StatementParameterException e){
			e.printStackTrace();
			throw new DataAccessObjectException("データ更新中にエラーが発生しました。:"+e.getMessage()+"\n"+parseSql+"\nパラメーター:\n"+parameters);
		} catch(Exception e){
			e.printStackTrace();
			throw new DataAccessObjectException("予期せぬエラーが発生しました。:"+e.getMessage()+"\n"+parseSql+"\nパラメーター:\n"+parameters);
		} finally {
			try {
				if ( preStmt != null ) preStmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"END");
		return result;
	}

	
	protected long execueSequence(String sequenceName) throws DataAccessException{
		String METHODNAME = "execueSequence";
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"START");		
		long newId=0;

		String sql = null;
		sql ="select nextval('"+sequenceName+"') as new_id";
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"SQL:"+sql);

		try {
			// 特殊なケースは rowSetをそのまま使用する。
			ReadOnlyRowSet rowSet = null;
			rowSet = executeSQL(sql);
			
			if ( rowSet.getMaxRows() >= 1 ){
				rowSet.first();
				newId = rowSet.getLong("new_id");
				DBGLog.fwdebug(CLASSNAME,METHODNAME,"newId:"+newId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessObjectException(""+e.getMessage());
		}

		
		DBGLog.fwdebug(CLASSNAME,METHODNAME,"END");
		return newId;
	}
	
	/**
	 * ArrayListからWhere条件作成
	 * @param array
	 * @return
	 */
	public static String makeWhere(ArrayList array){
		StringBuffer buf = new StringBuffer();
		for ( int i=0; i<array.size(); i++ ){
			String strValue = null;
			Object value = array.get(i);
			if ( value instanceof ArrayList ){
				ArrayList arrayValue = (ArrayList)value;
				strValue = "("+makeWhere(arrayValue)+")\n";
				strValue += " and "+strValue;
			}else if ( value instanceof String ){
				strValue = (String)value+"\n";
				if ( i != 0 ) strValue = " and "+strValue;
			}else if ( value instanceof StatementValue ){
				StatementValue stmtValue = (StatementValue)value;
				Object objValue = stmtValue.getValue();
				if ( objValue instanceof String ){
					strValue = (String)objValue+"\n";
				}else if ( objValue instanceof ArrayList ){
					strValue = "("+makeWhere((ArrayList)objValue)+")\n";
				}
				if ( i != 0 ) strValue = " "+stmtValue.getAppend()+" "+strValue;
			}

			if ( strValue != null )	buf.append(strValue);
		}
		return buf.toString();
	}
}
